from django.http import HttpResponse, JsonResponse
import jira
from jira import JIRA
import pymysql
import datetime
from . import config
import logging


logger = logging.getLogger(__name__)


jira = JIRA(config.jira_url, basic_auth=(config.basic_user, config.basic_passwd))


def get_project(request):
    data={}
    return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def get_index(request):
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)
    if request.method == 'GET':
        sumdata = request.GET.get("key[sumdata]",'')
        status_choice = request.GET.get("key[status]",'')
        search_content = request.GET.get("key[search_content]",'')
        if search_content:
            search_sql=' and (locate("{0}",b.name) or locate("{0}",a.issue_key)) '.format(search_content.upper())
        else:
            search_sql=""

        if not status_choice:
            pre_status="b.status!='关闭'"
        else:
            status_choice = status_choice.strip(',').split(',')
            pre_status="b.status in ('{}','未确认')".format("','".join(status_choice))

        if sumdata and 'on' in sumdata:
            cmd = "SELECT a.issue_key,b.name,group_concat(distinct a.reporter) reporter ,count(*) AS total,count(a.reopen>=1 or NULL) AS reopen," \
                  "count(a.status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7','10105','3') OR NULL)  AS unsolve, " \
                  "count(a.priority in ('危险','1') OR NULL)  AS fail, " \
                  "count(a.status in ('已解决','5') OR NULL)  AS unclose " \
                  "FROM dashboard_tasks a,dashboard_issues b where a.issue_key=b.code and b.active=1 and {} " \
                  "and a.reporter in (select name from dashboard_reporter where active=1) {} group by a.issue_key,b.name;  ".format(
                pre_status,search_sql)
        else:
            cmd = "SELECT a.issue_key,b.name,a.reporter,count(*) AS total,count(a.reopen>=1  or NULL) AS reopen," \
              "count(a.status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7','10105','3') OR NULL)  AS unsolve, " \
              "count(a.priority in ('危险','1') OR NULL)   AS fail, " \
              "count(a.status in ('已解决','5') OR NULL)  AS unclose " \
              "FROM dashboard_tasks a,dashboard_issues b where a.issue_key=b.code and b.active=1 and {} " \
              "and a.reporter in (select name from dashboard_reporter where active=1) {} group by a.issue_key,b.name,a.reporter;  ".format(pre_status,search_sql)

        # print (cmd.replace('\'','"'))
        cursor = connection.cursor()
        cursor.execute(cmd)
        data = dictfetchall(cursor)
        # print(data)

        cursor.close()
        connection.close()
        limit = request.GET.get("limit", 15)
        page = request.GET.get("page", 1)

        order1 = int(limit) * (int(page) - 1)
        order2 = int(limit) * (int(page))
        data = {
            "code": 0,
            "msg": "",
            "count": len(data),
            "data": data[order1:order2]}
        return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def get_index_echarts(request):
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)
    if request.method == 'POST' :
        datas=dict(request.POST)
        time_choise_set = '10'
        reporters=datas['reporter'][0]
        reporter=reporters.strip(',').split(',')
        reporter="','".join(reporter)
        project=datas['issue_key'][0]
        time_choise_info=datas['type'][0]
        if time_choise_info == time_choise_set:
            now_time = datetime.datetime.now()
            days = []
            for i in range(0, int(time_choise_info) ):
                oneday = datetime.timedelta(days=i)
                day = now_time - oneday
                date_to = datetime.datetime(day.year, day.month, day.day)
                days.append(str(date_to)[0:10])
            day_info="date_format(day, '%Y-%m-%d') in ('{}') and".format("','".join(days))
        else:
            day_info=""
        if ',' not in reporters:
            cmd = "SELECT date_format(day, '%m-%d'),issue_key,reporter,count(*) AS total,count(reopen>=1  or NULL) AS reopen," \
                  "count(status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7','10105','3') OR NULL)  AS unsolve, " \
              "count(priority in ('危险','1') OR NULL)  AS fail," \
              "count(status in ('已解决','5') OR NULL)  AS unclose " \
              " FROM" \
                  " dashboard_tasks where {} reporter in ('{}') and issue_key='{}'  " \
                  "group by day,issue_key,reporter; ".format(day_info, reporter, project)
        else:
            cmd = "SELECT date_format(day, '%m-%d'),issue_key,'',count(*) AS total,count(reopen>=1  or NULL) AS reopen," \
                  "count(status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7','10105','3') OR NULL)  AS unsolve, " \
                  "count(priority in ('危险','1') OR NULL)  AS fail," \
                  "count(status in ('已解决','5') OR NULL)  AS unclose " \
                  " FROM" \
                  " dashboard_tasks where {} reporter in ('{}') and issue_key='{}'  " \
                  "group by day,issue_key; ".format(day_info, reporter, project)

        print(cmd)
        cursor = connection.cursor()
        cursor.execute(cmd)
        report_info = cursor.fetchall()
        print(report_info)
        cursor.close()
        connection.close()
        total_bugs=[]
        days_info = []
        reopens=[]
        unsolves=[]
        unclose=[]
        fail_bugs=[]
        if time_choise_info == time_choise_set:
            j = 0
            length = len(report_info)
            days = sorted(days)
            day_length= len(days)
            for i in range(0, day_length):
                if j < length and days[i][5:10] == report_info[j][0]:
                    print (i,j)
                    days_info.append(report_info[j][0])
                    total_bugs.append(int(report_info[j][3]))
                    reopens.append(int(report_info[j][4]))
                    unsolves.append(int(report_info[j][5]))
                    fail_bugs.append(int(report_info[j][6]))
                    unclose.append(int(report_info[j][7]))
                    j += 1
                else:
                    days_info.append(days[i][5:10])
                    total_bugs.append(0)
                    reopens.append(0)
                    unsolves.append(0)
                    fail_bugs.append(0)
                    unclose.append(0)
        else:
            for i in report_info:
                days_info.append(i[0])
                total_bugs.append(int(i[3]))
                reopens.append(int(i[4]))
                unsolves.append(int(i[5]))
                fail_bugs.append(int(i[6]))
                unclose.append(int(i[7]))

        def get_all_data(datas):
            bugs_all=[datas[0]]
            for i in range(1,len(datas)):
                bugs_all.append(int(bugs_all[i-1])+int(datas[i]) )
            return bugs_all

        data={'title':'任务总单:{}'.format(project),
                        'subtitle':'测试人:{}'.format(reporters),
                                              'total_bugs':total_bugs,
                                              'reopens':reopens,
                                              'unsolves':unsolves,
                                              'xaxis':days_info,
                                              'fails':fail_bugs,
                                          'total_bugs_all': get_all_data(total_bugs),
                                          'reopens_all': get_all_data(reopens),
                                          'unsolves_all': get_all_data(unsolves),
                                          'fails_all': get_all_data(fail_bugs),
                                        'unclose_all': get_all_data(unclose)
                                              }
        return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def get_cpjf(request):
    limit = request.GET.get("limit", 15)
    page = request.GET.get("page", 1)
    search_content = request.GET.get("key[search_content]")
    status=request.GET.get("key[status]")
    reports={}
    if status:
        status=status.strip(',')
        add_command='AND issuetype in ({})'.format(status)
    else:
        add_command=''
    logger.info("+++++ request_begin: [{}] [{}]".format(request.path, request.method))
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)
    cursor = connection.cursor()
    class Issue():
        def __init__(self, args):
                    self.summary = args[0]
                    self.create_time = str(args[1])[0:10] if args[1] else ''
                    self.status = args[2]
                    self.project = args[3]
                    self.creator = args[4]
                    self.assignee = args[5]
                    self.reporter = args[6]
                    self.key = args[7]

    cursor.execute("SELECT project from dashboard_cpjf where active=1;")
    connection.commit()
    cursor.close()
    need_project = cursor.fetchall()
    connection.close()
    pro_len=len(need_project)
    need_projects=[ i[0] for i in need_project]
    logger.info("+++++ request_begin: [{}] [{}] {}".format(request.path, request.method, need_projects))
    projects=[]
    project_infos=[]

    for i in range(0,pro_len):
        projects.append(need_projects[i])
        if i>=20 or i >=pro_len-1:
            '''怀疑确认中状态已被删除'''
            # rule = 'project=CPJF and status not in (关闭, 已发布, 一线确认中, 发包核查中, 确认中) AND 项目名称 in ("{}") {}'.format(
            rule = 'project=CPJF and status not in (关闭, 已发布, 一线确认中, 发包核查中) AND 项目名称 in ("{}") {}'.format(
            '","'.join(projects), add_command)
            logger.info("+++++ request_begin: [{}] [{}] {}".format(request.path, request.method, rule))
            issue_result=jira.search_issues(rule, maxResults=False)
            for j in issue_result:
                project_infos.append(j)
            projects=[]
            logger.info("+++++ project_infos: {}".format(project_infos))
    for issue in project_infos:
        issue = jira.issue(issue)
        project=issue.fields.customfield_10402[0].value
        if project not in need_projects:
            continue
        obj = Issue([issue.fields.summary, issue.fields.created, issue.fields.status.name,
                     project,
                     issue.fields.creator.displayName, issue.fields.assignee.displayName,
                     issue.fields.reporter.displayName,issue.key])

        if not reports.get(project):
            reports.update({project: []})
        reports[project].append(obj)

    datas=[]
    for k,v in reports.items():
        for i in v:
            datas.append({"project":k,
                 "issue_key":i.key,
                 "summary":i.summary,
                "reporter":i.reporter,
                 "assignee":i.assignee,
                  "status":i.status,
                  "day":i.create_time
            })

    order1 = int(limit) * (int(page) - 1)
    order2 = int(limit) * (int(page))
    if search_content:
        search_content = search_content.strip().lower()
        result = []
        for i in datas:
            data_lists = ','.join(str(s).lower() for s in i.values() if s)
            if search_content in data_lists:
                result.append(i)
        data = {
            "code": 0,
            "msg": "",
            "count": len(result),
            "data": result[order1:order2]}
    else:
        data={"msg": "",
              "data":datas[order1:order2],
              "code": 0,
              "count": len(datas)}
    return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def get_dashboard(request):
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)

    cursor = connection.cursor()

    if request.method == 'GET':
        status_choice = request.GET.get("key[status]",'')
        sumdata = request.GET.get("key[sumdata]",'')
        search_content = request.GET.get("key[search_content]", '')

        if status_choice:
            status_choice = status_choice.strip(',').split(',')
            status_info = "and b.status in ('{}','未确认','1')".format("','".join(status_choice))
        else:
            status_info="and b.status not in ('关闭','7','6')"

        if search_content:
            search_sql = ' and (locate("{0}",b.name) or locate("{0}",a.issue_key)) '.format(search_content.upper())
        else:
            search_sql = ""

        if sumdata and 'on' in sumdata:
            cmd = "SELECT a.issue_key,b.name,b.project version,b.user,b.reporter,group_concat(distinct a.bug_owner) bug_owner,count(*) AS total,count(a.reopen>=1  or NULL) AS reopen," \
                  "count(a.status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7') OR NULL)  AS unsolve, count(a.priority in ('危险','1') OR NULL)  AS fail " \
                  "FROM dashboard_tasks a,dashboard_issues b where a.issue_key=b.code and " \
                  "b.project in ( select code from dashboard_project where active=1) and " \
                  "b.active=1 {} {} group by a.issue_key,b.name,b.project,b.user,b.reporter;".format(
                status_info,search_sql)

        else:
            cmd = "SELECT a.issue_key,b.name,b.project version,b.user,b.reporter,a.bug_owner,count(*) AS total,count(a.reopen>=1  or NULL ) AS reopen," \
                  "count(a.status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7') OR NULL)  AS unsolve, count(a.priority in ('危险','1') OR NULL)  AS fail " \
                  "FROM dashboard_tasks a,dashboard_issues b where a.issue_key=b.code and " \
              "b.project in ( select code from dashboard_project where active=1) and " \
              "b.active=1 {} {} group by a.issue_key,b.name,b.project,b.user,b.reporter,a.bug_owner;".format(status_info,search_sql)

        cursor.execute(cmd)

        data = dictfetchall(cursor)
        cursor.close()
        connection.close()
        # limit = request.GET.get("limit",10)
        # page = request.GET.get("page",1)
        # search_content=request.GET.get("key[search_content]")
        # order1 = int(limit) * (int(page) - 1)
        # order2 = int(limit) * (int(page))
        limit = request.GET.get("limit", 10)
        page = request.GET.get("page", 1)

        order1 = int(limit) * (int(page) - 1)
        order2 = int(limit) * (int(page))
        data = {
            "code": 0,
            "msg": "",
            "count": len(data),
            "data": data[order1:order2]}
        return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def get_dashboard_echarts(request):
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)
    if request.method == 'POST':
        datas = dict(request.POST)
        time_choise_set = '10'
        project = datas['issue_key'][0]
        time_choise_info = datas['type'][0]
        if time_choise_info == time_choise_set:
            now_time = datetime.datetime.now()
            days = []
            for i in range(0, int(time_choise_info) ):
                oneday = datetime.timedelta(days=i)
                day = now_time - oneday
                date_to = datetime.datetime(day.year, day.month, day.day)
                days.append(str(date_to)[0:10])
            day_info=" and date_format(day, '%Y-%m-%d') in ('{}') ".format("','".join(days))
        else:
            day_info=""

        cmd = "SELECT date_format(day, '%m-%d'),issue_key,count(*) AS total,count(reopen>=1  or NULL) AS reopen," \
                  "count(status not in ('重新打开' ,'已解决' ,'关闭','4','5','6','7') OR NULL)  AS unsolve, count(priority in ('危险','1') OR NULL)  AS fail FROM" \
                  " dashboard_tasks where issue_key='{}' {} " \
                  "group by day,issue_key; ".format(project,day_info)
        cursor = connection.cursor()
        cursor.execute(cmd)
        report_info = cursor.fetchall()
        cursor.close()
        connection.close()
        total_bugs = []
        days_info = []
        reopens = []
        unsolves = []
        fail_bugs = []

        def get_all_data(datas):
            bugs_all = [datas[0]]
            for i in range(1, len(datas)):
                bugs_all.append(int(bugs_all[i - 1]) + int(datas[i]))
            return bugs_all

        if time_choise_info == time_choise_set:
            j = 0
            length = len(report_info)
            days = sorted(days)
            day_length = len(days)
            for i in range(0, day_length):
                if j < length and days[i][5:10] == report_info[j][0]:
                    days_info.append(report_info[j][0])
                    total_bugs.append(int(report_info[j][2]))
                    reopens.append(int(report_info[j][3]))
                    unsolves.append(int(report_info[j][4]))
                    fail_bugs.append(int(report_info[j][5]))
                    j += 1
                else:
                    days_info.append(days[i][5:10])
                    total_bugs.append(0)
                    reopens.append(0)
                    unsolves.append(0)
                    fail_bugs.append(0)
        else:
            for i in report_info:
                days_info.append(i[0])
                total_bugs.append(int(i[2]))
                reopens.append(int(i[3]))
                unsolves.append(int(i[4]))
                fail_bugs.append(int(i[5]))

        data = {'title': '任务总单:{} '.format(project),
                'total_bugs': total_bugs,
                'reopens': reopens,
                'unsolves': unsolves,
                'xaxis': days_info,
                'fails': fail_bugs,
                'total_bugs_all': get_all_data(total_bugs),
                'reopens_all': get_all_data(reopens),
                'unsolves_all': get_all_data(unsolves),
                'fails_all': get_all_data(fail_bugs),
                }
        return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})


def dictfetchall(cursor):
     columns = [col[0] for col in cursor.description]
     return [
         dict(zip(columns, row))
         for row in cursor.fetchall()
         ]


def get_service(request):
    limit = request.GET.get("limit", 15)
    page = request.GET.get("page", 1)
    search_content = request.GET.get("key[search_content]")
    status=request.GET.get("key[status]")
    reports={}
    if status:
        status=status.strip(',')
        add_command='AND issuetype in ({})'.format(status)
    else:
        add_command=''
    connection = pymysql.connect(host=config.host, user=config.user,
                                 password=config.password, db=config.db, port=config.port)
    cursor = connection.cursor()
    class Issue():
        def __init__(self, args):
                    self.summary = args[0]
                    self.create_time = str(args[1])[0:10] if args[1] else ''
                    self.status = args[2]
                    self.project = args[3]
                    self.creator = args[4]
                    self.assignee = args[5]
                    self.reporter = args[6]
                    self.key = args[7]

    cursor.execute("SELECT project from dashboard_cpjf where active=1;")
    connection.commit()
    cursor.close()
    need_project = cursor.fetchall()
    connection.close()
    need_projects=[ i[0] for i in need_project]
    rule='project=CPJF and status not in (关闭, 已发布, 一线确认中, 发包核查中, 确认中) AND 项目名称 in ("{}") {}'.format('","'.join(need_projects),add_command)
    project_infos = jira.search_issues(rule, maxResults=False)
    for issue in project_infos:
        issue = jira.issue(issue)
        project=issue.fields.customfield_10402[0].value
        if project not in need_projects:
            continue
        obj = Issue([issue.fields.summary, issue.fields.created, issue.fields.status.name,
                     project,
                     issue.fields.creator.displayName, issue.fields.assignee.displayName,
                     issue.fields.reporter.displayName,issue.key])

        if not reports.get(project):
            reports.update({project: []})
        reports[project].append(obj)

    datas=[]
    for k,v in reports.items():
        for i in v:
            datas.append({"project":k,
                 "issue_key":i.key,
                 "summary":i.summary,
                "reporter":i.reporter,
                 "assignee":i.assignee,
                  "status":i.status,
                  "day":i.create_time
            })

    order1 = int(limit) * (int(page) - 1)
    order2 = int(limit) * (int(page))
    if search_content:
        search_content = search_content.strip().lower()
        result = []
        for i in datas:
            data_lists = ','.join(str(s).lower() for s in i.values() if s)
            if search_content in data_lists:
                result.append(i)
        data = {
            "code": 0,
            "msg": "",
            "count": len(result),
            "data": result[order1:order2]}
    else:
        data={"msg": "",
              "data":datas[order1:order2],
              "code": 0,
              "count": len(datas)}
    return JsonResponse(data, safe=False, json_dumps_params={'ensure_ascii': False})

